<?php 
class admin_functions{
     function check_user_profile($username,$password,$profile_id){
         ##########
        security::record_logs($username,"check_user_profile","checks the log in credentials","entering the system"); 
         ##########
      #this function checks the dbse table user profile if valid user exists
           $query_profile = sql_functions::select_query("* from user_profile where username='".$username."' and password=md5('".$password."') and profile_id='".$profile_id."'");
             $u_type=sql_functions::fetch_to_array($query_profile); 
           
           #this will record failed attempts
           if(sql_functions::count_rows($query_profile)==0){
                security::increment_failed_ip($_SERVER['REMOTE_ADDR']);               
                return false;
           }else{               
               $query = sql_functions::select_query("* from session_table where data like 'username%".$username."%password%".$password."%'");
               
               #creates sesssion in case the profile is not active
               if(sql_functions::count_rows($query)<>1){
                    $_SESSION['username']=$username;
                    $_SESSION['password']=$password; 
                    $_SESSION['profile_id']=$profile_id;
                    $_SESSION['syear']=date('Y');                  
                    $_SESSION['user_type']=$u_type[0]['user_type']; 
                    $_SESSION['name']=$u_type[0]['username'];
                    
                    #reset the counter
                        security::reset_ip_counter($_SERVER['REMOTE_ADDR']);
                    return true;
                    
               }else{
                   die("<p class='warnings'>Your profile is currently in use.</p>");    
               }                                       
           }   
     
     }
      
      function log_out($username,$password){
        #this function will make the row null
            sql_functions::apply_query("update user_profile set last_log=now() where username='".$username."' and password=md5('".$password."')");    
      }
      
      function get_users(){
        #this function will get rows from the codes table                 
        $query= sql_functions::select_query("* from codes where category='user_type' order by description");
        $rows = sql_functions::fetch_to_array($query);
        return  $rows;
      }
      
      function add_edit_delete_users($data,$action){ 
             #this function add users to the user_profile 
            if($action=="add"){
                 foreach($data as $icheck=>$susi){
                if($susi==''){
                    die("<p class='warnings'>You have an empty entry for: ".strtoupper(str_replace("_"," ",$icheck))."</p>");
                }
            }
                 #check for duplicates
                 $query = sql_functions::select_query("* from user_profile where username='".$data['username']."'");
                 #count the result of duplicates if exists
                  $row=sql_functions::count_rows($query);
                  if($row>=1){
                      die("<p class='warnings'>You have an existing duplicate data from our profile.</p>");
                  }else{
                        sql_functions::apply_query("insert into user_profile(username)values('".$data['username']."')");
                        $str = "update user_profile set password=md5('".$data['password']."'),user_type='".$data['user_type']."',name='".$data['fullname']."',profile_id = '".$data['profile_id']."',positions='".$data['position']."',phone='".$data['phone']."',email='".$data['email']."' where username='".$data['username']."'";
                        sql_functions::apply_query($str); 
                        echo "YOu have successfully added a user";
                  }                                    
                                                                                                           
            }elseif($action=="edit"){
                   $query = sql_functions::select_query("* from user_profile where username='".$data['old_username']."'");
                   $row=sql_functions::count_rows($query);
                  if($row==0){
                      die("<p class='warnings'>No records found for the entered username.</p>");
                  }elseif($row==1){                   
                      $str = "update user_profile set username='".$data['username']."',user_type='".$data['user_type']."',password=md5('".$data['password']."'),profile_id='".$data['profile_id']."',name='".$data['fullname']."',positions='".$data['position']."',phone='".$data['phone']."',email='".$data['email']."' where username='".$data['old_username']."'";
                      sql_functions::apply_query($str); 
                      echo "YOu have successfully update a user";
                  }else{
                       die("<p class='warnings'>Duplicate records found during update.</p>");
                  }
                  
            }else{
                #delet the user from user_profile
                sql_functions::apply_query("delete from user_profile where username='".$data['username']."'");
                echo "YOu have successflly deleted a user";
            }
      }
      
      function get_users_profile(){
           #this function edit the existing users from the user profile
           $query = sql_functions::select_query("* from user_profile order by username asc");
           $row=sql_functions::fetch_to_array($query);
           return $row;
      }
      
      function get_specific_profile($username){
          #this function get the userprofile a client
          $query = sql_functions::select_query("* from user_profile where username='".$username."'");
          $row=sql_functions::fetch_to_array($query);
          return $row;

      }
      
      function get_assessment(){
          $query=sql_functions::select_query("* from assessment order by assessmentname");
          $row=sql_functions::fetch_to_array($query);
          return $row;
      }
      
      function enable_assessment($datum,$semester){
          #this function will insert data for configuration
           #manage the posted data
           if($datum['total_assessment']<>''){
               $config=array();    
              foreach($datum['total_assessment'] as $id){
                  array_push($config,array('assessment_id'=>$id,'assessment_name'=>$_POST['descr_'.$id],'first'=>$_POST['first_'.$id],'second'=>$_POST['second_'.$id],'third'=>$_POST['third_'.$id],'fourth'=>$_POST['fourth_'.$id],'fifth'=>$_POST['fifth_'.$id]));
              } 
              //todo: may write delete all first
              foreach($config as $data){
                  sql_functions::apply_query("insert into config_assessment(assess_id,assess_name,first_amnt,second_amnt,third_amnt,fourth_amnt,fifth_amnt,semester,year,assess_type)values('".$data['assessment_id']."','".$data['assessment_name']."','".$data['first']."','".$data['second']."','".$data['third']."','".$data['fourth']."','".$data['fifth']."','".$semester."','".$_SESSION['syear']."','enrollment')");
              }
           }else{
               echo "You have not added a list";
           }                                                                                                                                                                                                                                                                                                                                             
      }
      
      function get_subjects_form($data){
                #this function enroll the student and assess, i.e inserts data to tables in registrar and cashier for official record
                $subjects=array();
                foreach($data['subject_code'] as $subj_code){
                       array_push($subjects,$subj_code);
                }
                $subjects=implode("|",$subjects);
                sql_functions::apply_query("update enroll_".$_SESSION['syear']." set subject=concat(subject,'|','".$subjects."'),date_registration='".date('Y-m-d')."',assessor='".$_SESSION['name']."' where student_id='".$data['student_id']."' and yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."'");
                $subjects=explode("|",$subjects);
                return $subjects;
      }
      
      function insert_to_collectibles($data){
             #create new table for collectibles and collection
              sql_functions::apply_query("create table if not exists  collectibles_".$_SESSION['syear']." like collectibles_year","cashier");
              sql_functions::apply_query("create table if not exists  collection_".$_SESSION['syear']." like collection_year","cashier");
            #this function will insert all data needed for enrollment to start
            $query = sql_functions::select_query("* from collectibles_".$_SESSION['syear']./*date('Y')*/" where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and student_id='".$data['student_id']."'","cashier");
            $count=sql_functions::count_rows($query);
            if($count<>0){
             #delete existing records
             //sql_functions::apply_query("delete from collectibles_".$_SESSION['syear']./*date('Y')*/" where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and student_id='".$data['student_id']."'","cashier");   
            }
             $bayad=array();
            foreach($data['assess_id'] as $bayadan){
                    array_push($bayad,array('assess_id'=>$bayadan,'assess_name'=>$data['descr_'.$bayadan],'amount'=>$data['amount_'.$bayadan]));
            } 
            #insert the reocrd to collectibles
            foreach($bayad as $bayaran){
                sql_functions::apply_query("insert into collectibles_".$_SESSION['syear']./*date('Y')*/"(student_id,assess_id,assess_name,amount,yr_lvl,semester,assess_type)values('".$data['student_id']."','".$bayaran['assess_id']."','".$bayaran['assess_name']."','".sprintf("%.2f",$bayaran['amount'])."',".$data['yr_lvl'].",".$data['semester'].",'enrollment')","cashier");
            }
            #checks if a ledger exists 
            #checks whether a student ledger exists 
              $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_cash_sis' and table_name='stud_".$data['student_id']."'","information_schema");                 
              $table_count = sql_functions::fetch_to_array($table_exists);
              if($table_count[0]['bilang']==0){
                sql_functions::apply_query("create table if not exists stud_".$data['student_id']." like ledger_template","cashier");
              }
               foreach($bayad as $bayaran){
                sql_functions::apply_query("insert into stud_".$data['student_id']./*date('Y')*/"(assess_id,assess_name,amount,yr_lvl,semester,assess_type)values('".$bayaran['assess_id']."','".$bayaran['assess_name']."','".sprintf("%.2f",$bayaran['amount'])."',".$data['yr_lvl'].",".$data['semester'].",'enrollment')","cashier");
            }
            echo "YOu are officially enrolled enrolled";
            #check i 
      }
      
      function check_column_taken($data,$subjects){
          $clean = array();
           foreach($subjects as $subject){
               array_push($clean,"'".$subject."'");
           }
         $str= "update stud_".$data['student_id']." set taken_1=concat(taken_1,'".date('Y')."-".$data['semester']."-".$data['section']."') where (yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."') and subj_code in (".implode(",",$clean).")";
          sql_functions::apply_query($str);                   
      }           
    
     function admission_processing($_POST){
          $sname = admin_functions::check_input_string($_POST['sname']) ? die("<p class='warnings'>Check your entry in surname field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a></p>"): mysql_escape_string($_POST['sname']);
           $fname = admin_functions::check_input_string($_POST['fname']) ? die("<p class='warnings'>Check your entry in firstname field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['fname']);
           $mname =  $_POST['mname'];         
          $name = $sname.", ".$fname." ".$mname;
     
                    $age = admin_functions::check_input_numeric($_POST['age']) ? die("<p class='warnings'>Check your entry in age field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['age']);
                    $d_birth = admin_functions::check_input_date($_POST['d_birth']) ? die("<p class='warnings'>Check your entry in date of birth field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['d_birth']);
                   // $height = admin_functions::check_input_numeric($_POST['height']) ? die("<p class='warnings'>Check your entry in height field!</p>"): mysql_escape_string($_POST['height']);
                   $height = str_replace("'","\'",$_POST['height']);
                    $gender = admin_functions::check_input_string($_POST['gender']) ? die("<p class='warnings'>Check your entry in gender field!</p> <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a"): mysql_escape_string($_POST['gender']);
                   // $weight = admin_functions::check_input_numeric($_POST['weight']) ? die("<p class='warnings'>Check your entry in weight field!</p>"): mysql_escape_string($_POST['weight']);
                   $weight = $_POST['weight'];
                    $religion = admin_functions::check_input_string($_POST['religion']) ? die("<p class='warnings'>Check your entry in religion field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['religion']);
                    $m_address = admin_functions::check_input_string($_POST['m_address']) ? die("<p class='warnings'>Check your entry in mailing address field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['m_address']);
                    $h_address = admin_functions::check_input_string($_POST['h_address']) ? die("<p class='warnings'>Check your entry in home address field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['h_address']);
                    $c_number = admin_functions::check_input_numeric($_POST['c_number']) ? die("<p class='warnings'>Check your entry in cellphone number field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['c_number']);
                    $e_address = admin_functions::check_input_string($_POST['e_address']) ? die("<p class='warnings'>Check your entry in email address field! <a href='#' onclick='history.go(-1);return false;'>[Go Back]</a</p>"): mysql_escape_string($_POST['e_address']);
                   // $f_name = admin_functions::check_input_string($_POST['f_name']) ? die("<p class='warnings'>Check your entry in father\'s name field!</p>"): mysql_escape_string($_POST['f_name']);;
                   $f_name=$_POST['f_name'];
                  //  $f_occupation = admin_functions::check_input_string($_POST['f_occupation']) ? die("<p class='warnings'>Check your entry in father\'s occupation field!</p>"): mysql_escape_string($_POST['f_occupation']);
                  $f_occupation = $_POST['f_occupation'];
                 //   $m_name = admin_functions::check_input_string($_POST['m_name']) ? die("<p class='warnings'>Check your entry in mother\'s name field!</p>"): mysql_escape_string($_POST['m_name']);
                 $m_name = $_POST['m_name'];
                 //   $m_occupation = admin_functions::check_input_string($_POST['m_occupation']) ? die("<p class='warnings'>Check your entry in mother\'s occupation field!</p>"): mysql_escape_string($_POST['m_occupation']);
                 $m_occupation = $_POST['m_occupation'];
                   // $g_name = admin_functions::check_input_string($_POST['g_name']) ? die("<p class='warnings'>Check your entry in guardian\'s name field!</p>"): mysql_escape_string($_POST['g_name']);
                   $g_name = $_POST['g_name'];
                  //  $relationship = admin_functions::check_input_string($_POST['relationship']) ? die("<p class='warnings'>Check your entry in guardian\'s relationship field!</p>"): mysql_escape_string($_POST['relationship']);
                  $relationship = $_POST['relationship'];
                    $f_choice= $_POST['f_choice'];
                    $s_choice= $_POST['s_choice'];
                    $t_choice= $_POST['t_choice'];
                    $remarks = $_POST['remarks'];
                    
                    #algorithm to generate registration number
                    $get_admission_no = sql_functions::select_query(" max(id)+1 as pinakadako FROM admission_profile");
                    $get_admission_no = sql_functions::fetch_to_array($get_admission_no);
                    $reg_no = date("Y")."-".date("m")."-".sprintf("000%d",$get_admission_no[0]['pinakadako']);
                    $date_registration = date("Y-m-d");
                    // will insert safe strings to database
                    $sql_str_1 = "insert into admission_profile(firstname,middlename,surname,name,age,d_birth,height,gender,weight,nationality,religion,m_address,h_address,c_number,e_address";
                    $sql_str_2=",f_name,f_occupation,m_name,m_occupation,g_name,relationship,f_choice,s_choice,t_choice,remarks,date_registration,registration_no)values('fname','$mname','sname','$name','$age','$d_birth','$height','$gender',";
                    $sql_str_3="'$weight','Filipino','$religion','$m_address','$h_address','$c_number','$e_address','$f_name','$f_occupation','$m_name',";
                    $sql_str_4=" '$m_occupation','$g_name','$relationship','$f_choice','$s_choice','$t_choice','$remarks','$date_registration','$reg_no')";
                    //echo $sql_str_1.$sql_str_2.$sql_str_3.$sql_str_4;
                    $sql_str_query= sql_functions::apply_query($sql_str_1.$sql_str_2.$sql_str_3.$sql_str_4);
                    //end of database 
                    
                    #translate the codes of course choices
                    $sql_first = sql_functions::fetch_to_array(sql_functions::select_query("degreename from courses where degreeid='".$f_choice."'"));
                     $sql_second = sql_functions::fetch_to_array(sql_functions::select_query("degreename from courses where degreeid='".$s_choice."'"));
                     $sql_third = sql_functions::fetch_to_array(sql_functions::select_query("degreename from courses where degreeid='".$t_choice."'"));
                    #ready to return data for download
                    $data = array();
                    $data=array('name'=>$name,'gender'=>$gender,'address'=>$h_address,'first_choice'=>$sql_first[0]['degreename'],'second_choice'=>$sql_second[0]['degreename'],'third_choice'=>$sql_third[0]['degreename'],'registration_no'=>$reg_no,'date_of_registration'=>$date_registration);
                    return $data;     
                    
            }

            function check_input_string($input){
                #this function checks tthe input if a valid string
                $input =(!isset($input)||trim($input)=="") ? true: false;
                return $input;
            }
            
            function check_input_numeric($input){
                #this function checs the input if a valid number
                $input =(!isset($input)||trim($input)==""||!is_numeric($input)) ? true: false;
                return $input;
            }
                
            function check_input_date($input){
                $input = explode("-","$input");
                 $input =(!isset($input)||!checkdate($input[0],$input[1],$input[2])) ? true: false;
                return $input;
            } 
            
            function get_courses_option(){
                #this function gets the courses from the dbase
                $query = sql_functions::select_query("* from courses where remarks='enable'");
                $course = sql_functions::fetch_to_array($query);
                return $course;
            }
            
            function get_admission_names(){
                #this function gets the names of students as pre-enrolled
                $query = sql_functions::select_query("* from admission_profile");
                $data=sql_functions::fetch_to_array($query);
                return $data;
                
            }
            
            function get_erolled_names(){
                #this function gets the names of students as pre-enrolled
                $query = sql_functions::select_query("enroll.*,reg.name from enroll_".$_SESSION['syear']." as enroll inner join reg_profile as reg on enroll.student_id=reg.stud_id where (enroll.section is not null or enroll.section<>'')"/*date('Y')*/);
                $data=sql_functions::fetch_to_array($query);
                return $data;
            }
            
            function assess_student($data){
              #this function will assess the student
              #this will check first if a student ledger exists
               #checks whether a student table exists 
              $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_cash_sis' and table_name='stud_".$data['student_id']."'","information_schema");                 
              $table_count = sql_functions::fetch_to_array($table_exists);
              if($table_count[0]['bilang']==0){
                  die("<p class='warnings'>YOu have no assessment details!</p>");
              }else{ 
                  #checks first if there is current assessment
                 $query = sql_functions::select_query("* from stud_".$data['student_id']." where status='assessed' and assess_type='enrollment'","cashier");
                 if(true){ //commented to fix bug
                 // if(sql_functions::count_rows($query)==0){
                      $query = sql_functions::select_query("assess_id,assess_name,truncate(if(status='partial' or status='',amount-accounting_amount,amount),2) as amount from stud_".$data['student_id']." where  yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and  status='' and assess_type='enrollment'","cashier");
                           if(sql_functions::count_rows($query)==0){
                               die("<p class='warnings'>You have no acount balances and no accounts request.</p>");
                           }else{
                             $row=sql_functions::fetch_to_array($query);                                                                   
                             return $row;
                            } 
                  }else{
                     die("<p class='warnings'>YOu have current assessment</p>");
                  } 
              } 
            }
            
            function update_collectibles_assessment($data){
               #this function will update the collebtbles table for the assessment
               $payments=array();
                foreach($data['assess_id'] as $bayaran){
                    array_push($payments,array('assess_id'=>$bayaran,'assess_no'=>$data['assess_no'],'assess_amount'=>sprintf("%.2f",$data['amount_'.$bayaran]),'assess_date'=>date('Y-m-d')));
                } 
                #update the collectibles table remarks
                foreach($payments as $lalagyan){
                    #remarks
                    sql_functions::apply_query("update collectibles_".$_SESSION['syear']." set assess_no='".$lalagyan['assess_no']."',assess_amount='".sprintf("%.2f",$lalagyan['assess_amount'])."',assess_date='".$lalagyan['assess_date']."',remarks='assessed' where student_id='".$data['student_id']."' and yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$lalagyan['assess_id']."' and assess_type='enrollment' and remarks=''" ,"cashier");  
                    #remaining balnce if there is
                    sql_functions::apply_query("insert into collectibles_".$_SESSION['syear']."(student_id,assess_id,assess_name,amount,yr_lvl,semester,assess_type)select '".$data['student_id']."','73','Tuition Fee Balance',(select truncate(sum(amount)-sum(assess_amount),2) from collectibles_".$_SESSION['syear']." where student_id='".$data['student_id']."' and yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$lalagyan['assess_id']."' and assess_type='enrollment'),".$data['yr_lvl'].",".$data['semester'].",'enrollment' from collectibles_".$_SESSION['syear']." where student_id='".$data['student_id']."' and yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$lalagyan['assess_id']."' and assess_type='enrollment' and remarks<>'paid' and remarks='assessed' and amount<>assess_amount","cashier");
                }
               #update the student ledger table status
                foreach($payments as $lalagyan){
                  sql_functions::apply_query("update stud_".$data['student_id']." set accounting_no='".$lalagyan['assess_no']."',accounting_amount='".sprintf("%.2f",$lalagyan['assess_amount'])."',accounting_date='".$lalagyan['assess_date']."',status='assessed' where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$lalagyan['assess_id']."' and assess_type='enrollment' and  status=''","cashier");  
                    #remaining balnce if there is
                    sql_functions::apply_query("insert into stud_".$data['student_id']."(assess_id,assess_name,amount,yr_lvl,semester,assess_type)select '73','Tuition Fee Balance',(select truncate(sum(amount)-sum(accounting_amount),2) from stud_".$data['student_id']." where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$lalagyan['assess_id']."' and assess_type='enrollment'),".$data['yr_lvl'].",".$data['semester'].",'enrollment' from stud_".$data['student_id']." where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$lalagyan['assess_id']."' and assess_type='enrollment' and status<>'paid' and status='assessed' and amount<>accounting_amount","cashier");
                }
                
                echo "You have successfully assess the student";
                
            }
            
            function get_cashier_payments($student_id,$assess_no){
                #this function will get the earliest receivable from student
                 #checks whether a student table exists 
                  $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_cash_sis' and table_name='stud_".$student_id."'","information_schema");                 
                  $table_count = sql_functions::fetch_to_array($table_exists);
                  if($table_count[0]['bilang']==0){
                    die("<p class='warnings'>YOu have no assessment details!</p>"); 
                  }else{
                     $query = sql_functions::select_query("* from stud_".$student_id." where accounting_no = '".$assess_no."' and (status='assessed')" ,"cashier");
                     $row = sql_functions::fetch_to_array($query);
                     return $row;
                  } 
            }
            
            function pay_all($data){
                 #thiw will update student ledgertable
                    sql_functions::apply_query("update stud_".$data['student_id']." set or_date='".date("m-d-Y")."',or_number='".$data['or_no']."', status='paid' where accounting_no = '".$data['assess_no']."'","cashier");
                 #update collectibles
                     sql_functions::apply_query("update collectibles_".$_SESSION['syear']." set remarks='paid' where assess_no = '".$data['assess_no']."' and student_id='".$data['student_id']."'","cashier");
                
                 #prepare the array name for insertion
                 $a =array();
                 foreach($data['assess_name'] as $assess_name){
                     array_push($a,$assess_name);
                 }
       /*          #start of algorithm
                        #the function to count characters
                        function bilanga($count){
                            return strlen($count);
                        };
                        #the function to get the part names of each element in array
                        function get_each($value){
                            if(strlen($value)<=kahalaba){
                                return $value;
                            }else{
                                return substr($value,0,kahalaba);
                            }
                        }
                        #this is the actual call of the algorithm
                        $numero = array_map("bilanga",$a);
                        #this is the count of the elements of the array
                        $total = count($numero);
                        #this will give the total of the characters of array returned by the function
                        foreach($numero as $bilang){
                            $count = $bilang + $count;
                        }
                        define("kahalaba",round($count/$total));
                        #algorithm for getting the words
                        $filtered = array_map("get_each",$a);
                        $name = implode(",",$filtered); */
                      #end of algorithm  
                      $name = implode(",",$a);
                 #this will insert to collection
                 sql_functions::apply_query("insert into collection_".$_SESSION['syear']."(student_id,assess_id,assess_name,amount,yr_lvl,semester,assess_no,assess_date,or_number,or_date)values('".$data['student_id']."','0','".$name."','".sprintf("%.2f",$data['amount'])."',".$data['yr_lvl'].",".$data['semester'].",'".$data['assess_no']."','".$data['assess_date']."','".$data['or_no']."','".date('m-d-Y')."')","cashier");
            }
            
            function show_list_balances($data){
                  #this will get from the dbase collectibles_year
                  $query = sql_functions::select_query("*,sum(amount) as balance from collectibles_".$_SESSION['syear']." where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and (remarks='assessed' or remarks='') group by assess_no order by id asc","cashier");
                  $row = sql_functions::fetch_to_array($query);
                  return $row;
            }
            
            function show_collection($data){
                 #this will get from dbase all the collection
                 $query = sql_functions::select_query("* from collection_".$_SESSION['syear']." where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester'],"cashier");
                 $data= sql_functions::fetch_to_array($query);
                 return $data;
            }
            
            function create_exam($data){
                #this function will create a table for examination
                foreach($data as $susi=>$item){
                        switch($susi){
                            case 'exam_name':
                                if($item==''){                            
                                    die("<p class='warnings'>What is the exam name?</p>");
                                }
                                break;
                            case 'no_item':
                                if($item=='' || !is_numeric($item)){
                                    die("<p class='warnings'>How many items does the exam have?</p>");
                                }
                                break;
                            case 'no_choices':
                                 if($item=='' || !is_numeric($item)){
                                 die("<p class='warnings'>How many choices are there on each item?</p>");
                                 }
                                break;          
                        }
                   
                }
                    $name =trim($data['exam_name']);
                    $name = str_replace(" ","_",$name);
                    #checks if a table exists
                    $query = sql_functions::select_query("* from admission_exam_list where code_exam='".$name."'");
                    $row = sql_functions::count_rows($query);
                    if($row==0){
                            #choices
                            $no_items = trim($data['no_item']);
                            #this will now create the table      
                           sql_functions::apply_query("create table exam_".$name." like exam_name_year");
                           $choices = array(1=>'opt_a',2=>'opt_b',3=>'opt_c',4=>'opt_d',5=>'opt_e',6=>'opt_f');
                           #this will now add choices
                          foreach($choices as $choice=>$papilian){ 
                                if($choice<=$data['no_choices']){   
                                    sql_functions::apply_query(" ALTER TABLE  exam_".$name." ADD  ".$papilian." TEXT NOT NULL");      
                                }else{
                                    break;
                                }                 
                          }
                          for($i=1;$i<=$no_items;$i++){ 
                              sql_functions::apply_query("insert into exam_".$name."(no)values(".$i.")");
                          }
                          #insert to the profile
                          sql_functions::apply_query("insert into admission_exam_list(code_exam,name_exam)values('".$name."','".$data['exam_name']."')");
                          echo "successfully created an exam"; 
                    }else{
                        echo "Exam name already exists";
                    }
            }
            
            function get_exams_names(){
                #this will get all of the exam names from admission list table
                 $query = sql_functions::select_query("* from admission_exam_list");
                 $data = sql_functions::fetch_to_array($query);
                 return $data;
                 
            }
            
            function get_items_choices($subj_name){
                  #this will get all of the items in the subject table
                   $query = sql_functions::select_query("* from exam_".$subj_name);
                   $exam= sql_functions::fetch_to_array($query);
                   return $exam;
            }
            
            function add_items_dbase($data){ 
                #this function will insert the question to the dbase
                $count = $data['count']-1;
                $subject = "exam_".$data['subj'];
                $items = array("a","b","c","d","e","f");
                #clean the table
                sql_functions::apply_query("delete from ".$subject);
                #start the insert sql header
                $insert = "insert into ".$subject."(no,main_q,cor_ans";
                   foreach($items as $item){
                       if(is_null($data[$item."_1"])){
                   
                       }else{
                           $insert.= ",opt_".$item;
                       }
                   }
                   #end the insert header
                    $insert.=")values"; 
                    #prepare the data for insertion
                              for($i=1;$i<=$count;$i++) {
                                  $insert .= "(".$i.",'".$data['question_'.$i]."','".$data['answer_'.$i]."'";
                                        foreach($items as $item){
                                            if(is_null($data[$item."_".$i])){
                                                
                                            }else{
                                                $insert.= ",'".$data[$item."_".$i]."'";
                                            }
                                        }
                                    $insert.=")";    
                              }
                     #end of dta preparation by closing tag
                     $insert = str_replace(")(","),(",$insert);
                     sql_functions::apply_query($insert);
                     echo "Successfully updated the exam!";
            }
            
            function prepare_an_exam_now($data){
                    #this will render questions
                    $query = sql_functions::select_query("* from exam_".$data['exam_name']);
                    if(sql_functions::count_rows($query)==0){
                        die("<p class='warnings'>Invalid exam code.</p>");
                    }
                    $row = sql_functions::fetch_to_array($query);
                    #randomize the content of questions
                     shuffle($row);
                    #check and create a table and insert
                    if(is_numeric($data['student_id'])){
                         sql_functions::apply_query("creATe table if not exists admin_stud_".$data['student_id']." like admin_stud_no");
                    }else{
                           die("<p class='warnings'>Please change the student id no used.</p>");
                    }                                           
                    #delte old data
                        sql_functions::apply_query("delete from admin_stud_".$data['student_id']);    
                    #prepare insertion to temporaty table
                     $insert = "insert into admin_stud_".$data['student_id']."(item_no,cor_ans)values";
                         foreach($row as $ibubutang){
                                $insert.= "('".$ibubutang['no']."','".$ibubutang['cor_ans']."')";
                         }
                     #prepare for insertion
                        $insert = str_replace(")(","),(",$insert);
                     #insert the data
                     sql_functions::apply_query($insert);      
            } 
            
            function get_questions_to_render($data){
                #this will get the questions
                $query = sql_functions::select_query(" numero.item_no,haputan.* FROM admin_stud_".$data['student_id']." as numero left join exam_".$data['exam_name']." as haputan on numero.item_no = haputan.no");
                $hapot = sql_functions::fetch_to_array($query);
                return $hapot;
                
            }
            
            function insert_answers($data){
                #this will insert the answers of students
                        foreach($data['item_no'] as $answer){
                             sql_functions::apply_query("update admin_stud_".$data['student_id']." set response='".$data['ans_'.$answer]."' where item_no=".$answer);
                        }
                        #get the score
                        $score = sql_functions::select_query("count(*) as score from admin_stud_".$data['student_id']." where cor_ans = response");
                        $score = sql_functions::fetch_to_array($score);
                        $rating = sprintf("%.2f",((-4/($data['count']-1))* $score[0]['score'])+5);
                        if($rating<=3.0){
                            $remarks = "passed";
                        }else{
                            $remarks = "failed";
                        }
                        sql_functions::apply_query("insert into admission_name_exam(student_id,name,subject_id,score,no_items,rating,remarks)values('".$data['student_id']."','".$data['name']."','".$data['exam_name']."','".$score[0]['score']."','".($data['count']-1)."','".$rating."','".$remarks."')");
                        
            }
            
            function get_names_students(){
                #select names of students who have taken exam
                $query = sql_functions::select_query("* from admission_name_exam");
                $row = sql_functions::fetch_to_array($query);
                return $row;
            }
            
            function insert_cashier_profile($data,$profile){
                #this checks if a row exists in the cashier profile and then update or insert
                  $query = sql_functions::select_query("* from cash_profile where student_id='".$data['student_id']."'","cashier");
                  $row=sql_functions::count_rows($query);
                  if($row==0){
                     sql_functions::apply_query("insert into cash_profile(student_id,name,course,major)values('".$profile[0]['stud_id']."','".$profile[0]['name']."','".$profile[0]['course']."','".$profile[0]['major']."')","cashier");
                  }else{
                      
                  }
            }
            
            function generate_undergrad_collection($data){
                #this function generates collection from collection  table
                $query = sql_functions::select_query("a.or_number,b.name,concat(b.course,'-',b.major,' ',a.yr_lvl) as course,a.assess_name,a.amount,a.assess_date,a.assess_no,a.amount FROM ssc_cash_sis.collection_".$_SESSION['syear']." as a left join ssc_cash_sis.cash_profile as b on a.student_id=b.student_id where a.or_date='".$data['month']."-".$data['day']."-".$data['year']."'","cashier"); 
                $count = sql_functions::count_rows($query);
               

                #check if there is a record
                   if($count==0){
                       echo "YOu have no current collection details";
                   }else{
                        $records = sql_functions::fetch_to_array($query);
                        $excel = new download_excel("reports/collection.xls");
                        
                        #start the insertion to excel
                        #this is the column start  1
                        #this is the row start  13
                            $column= 1;
                            $row = 13;
                            $count = 1;
                            $page_locator = 9;
                            $total = 0;
                            $cur_page=1;
                            $next_page=1;
                            $date_column = 9;
                                             
                         #start the insertion of  records 
                            foreach($records as $record){
                               
                               if($next_page==$cur_page){ 
                                 $excel->set_cell_value('W'.$page_locator,$cur_page); //set the page of the sheet
                                 $excel->set_cell_value('B'.$page_locator,date('m-d-Y'));//set the date of the sheet
                                 
                                 #advance variable to next page
                                    $next_page++;
                               }  
                                
                                $excel->set_column_row_value($column,$row,$record['or_number']);
                                 $excel->set_column_row_value($column + 5,$row,$record['name']);
                                 $excel->set_column_row_value($column + 6,$row,$record['course']);
                                 $excel->set_column_row_value($column + 15,$row,$record['assess_name']);
                                 $excel->set_column_row_value($column + 16,$row,$record['amount']);
                                 $excel->set_column_row_value($column + 19,$row,$record['assess_date']);
                                 $excel->set_column_row_value($column + 20,$row,$record['assess_no']);
                                 $excel->set_column_row_value($column + 21,$row,$record['amount']); 
                                 
                                 #get the values of amount and sum
                                    $total = $total + $record['amount'];
                                 #proceed to next row
                                 $row++;       //row specifier
                                 $count++;     //count of each row specifier
                            
                                  #write the value of date and page number in each sheet oundaries  
                                 if($count==25){  
                                        $cur_page++;    //sets the page                                 
                                         $count=1;   //reset the counter
                                         $row = $row + 13; //set the cursor to the next specified location
                                         $page_locator = $page_locator + 37; //advance the next page locator
                                                                          
                                 }
                               }
                           
                           #force download and unload excel
                            $excel->force_download("collection_".$data['month']."-".$data['day']."-".$data['year']);
                            $excel->unload_excel();
                            echo $total;                
                   }
                            
                           
               }
                   
              function view_collectibles_page($data){
                   #this function generates collection from collection  table
                $query = sql_functions::select_query("a.or_number,b.name,concat(b.course,'-',b.major,' ',a.yr_lvl) as course,a.assess_name,a.amount,a.assess_date,a.assess_no,a.amount FROM ssc_cash_sis.collection_".$_SESSION['syear']." as a left join ssc_cash_sis.cash_profile as b on a.student_id=b.student_id where a.or_date='".$data['month']."-".$data['day']."-".$data['year']."'","cashier"); 
                $count = sql_functions::count_rows($query);
               

                #check if there is a record
                   if($count==0){
                       die("YOu have no current collection details");
                   }else{
                        $records = sql_functions::fetch_to_array($query);
                        return $records;
                   }      
                }
                
                
               function setting_configure($data){
                  #this function overrides the settings present date 
                   $_SESSION['syear']=$data['syear'];
                    echo "Successfully changed the default configuration";
               }
               
               
               function check_if_paid($assess_name,$data){
                   #this function will checked if a particular request has been made or paid
                       $query = sql_functions::select_query("* from collection_".$_SESSION['syear']." where student_id='".$data['student_id']."' and or_number='".$data['or_no']."' and assess_name like '%".$assess_name."%'","cashier");
                       $count = sql_functions::count_rows($query);
                       $row = sql_functions::fetch_to_array($query);
                       if($count==1){
                            $ibalik = array('count'=>true,'yr_lvl'=>$row[0]['yr_lvl'],'semester'=>$row[0]['semester']);
                       }else{
                            $ibalik = array('count'=>false,'yr_lvl'=>$row[0]['yr_lvl'],'semester'=>$row[0]['semester']);
                       } 
                       return $ibalik;
               }
               
               function assess_other_fees($data){
                   #this function gets the assessment of student for other fees.
                    #checks whether a student table exists 
              $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_cash_sis' and table_name='stud_".$data['student_id']."'","information_schema");                 
              $table_count = sql_functions::fetch_to_array($table_exists);
              if($table_count[0]['bilang']==0){
                  die("<p class='warnings'>YOu have no assessment details!</p>");
              }else{ 
                  $query= sql_functions::select_query("* from stud_".$data['student_id']." where assess_type='' and status='' and yr_lvl=".$data['yr_lvl']." and semester=".$data['semester'],"cashier");
                   $row = sql_functions::fetch_to_array($query);
                   if(sql_functions::count_rows($query)==0){
                       die("<p class='warnings'>You have no request made.</p>");
                   }else{
                         return $row;  
                   }
              }                 
               } 
               
               function update_ledger_collectibles_cog($data){
                   #this function will update the student ledger and collectibles wth cog assessment id=0,name=Certification fee
                   #this function will assess the student
                   #this will check first if a student ledger exists
                   #checks whether a student table exists 
                   $bayad = sprintf("%.2f",$data['bayad'] * $data['copies']);
                      $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_cash_sis' and table_name='stud_".$data['student_id']."'","information_schema");                 
                      $table_count = sql_functions::fetch_to_array($table_exists);
                      if($table_count[0]['bilang']==0){
                          die("<p class='warnings'>You have no assessment details and ledger!</p>");
                      }else{ 
                   
                        sql_functions::apply_query("insert into stud_".$data['student_id']."(assess_id,assess_name,amount,yr_lvl,semester)values('".$data['assess_id']."','".$data['pangaran']."','".$bayad."',".$data['yr_lvl'].",".$data['semester'].")","cashier");
                        
                        #this do the same in the part of collectibles table
                       sql_functions::apply_query("insert into collectibles_".$_SESSION['syear']."(student_id,assess_id,assess_name,amount,yr_lvl,semester)values('".$data['student_id']."','".$data['assess_id']."','".$data['pangaran']."','".$bayad."',".$data['yr_lvl'].",".$data['semester'].")","cashier");
                      }
                      
                      echo "You have successfully sent request for certification of grades.";
              }
              
              function update_ledger_collectibles_eval($data){
                   #this function will update the student ledger and collectibles wth cog assessment id=0,name=Certification fee
                   #this function will assess the student
                   #this will check first if a student ledger exists
                   #checks whether a student table exists 
                      $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_cash_sis' and table_name='stud_".$data['student_id']."'","information_schema");                 
                      $table_count = sql_functions::fetch_to_array($table_exists);
                      if($table_count[0]['bilang']==0){
                          die("YOu have no assessment details and ledger!");
                      }else{ 
                   
                        sql_functions::apply_query("insert into stud_".$data['student_id']."(assess_id,assess_name,amount,yr_lvl,semester)values(27,'Certification fee',50.00,".$data['yr_lvl'].",".$data['semester'].")","cashier");
                        
                        #this do the same in the part of collectibles table
                       sql_functions::apply_query("insert into collectibles_".$_SESSION['syear']."(student_id,assess_id,assess_name,amount,yr_lvl,semester)values('".$data['student_id']."',27,'Certification Fee',50.00,".$data['yr_lvl'].",".$data['semester'].")","cashier");
                      }
                      
                      echo "You have successfully sent request for certification of grades.";
              }
              
              function update_other_fees_assessed($data){
                  #this will update the collectibles and student ledger with assessed
               $payments=array();
               #prepare first the array
                foreach($data['assess_id'] as $bayaran){
                    array_push($payments,array('assess_id'=>$bayaran,'assess_no'=>$data['assess_no'],'assess_amount'=>sprintf("%.2f",$data['amount_'.$bayaran]),'assess_date'=>date('Y-m-d')));   
                }  
              #start insertion to the collectibles table
                foreach($payments as $payment){
                sql_functions::apply_query("update collectibles_".$_SESSION['syear']." set assess_no='".$payment['assess_no']."',assess_amount='".$payment['assess_amount']."',assess_date='".$payment['assess_date']."', remarks='assessed' where yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."' and assess_type='' and assess_id='".$payment['assess_id']."'and student_id='".$data['student_id']."'","cashier");  
                
               #start insertion to the student ledger
                sql_functions::apply_query("update stud_".$data['student_id']." set accounting_no='".$payment['assess_no']."',accounting_date='".$payment['assess_date']."' ,accounting_amount='".$payment['assess_amount']."', status='assessed' where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." and assess_id='".$payment['assess_id']."' and assess_type=''","cashier");
                
                }
                
                echo "You have successfully assessed the account.";
              }
              
              
               function mga_konstant($request){
                     if($request=="evaluation"){
                        define('titulo',"Request for Evaluation of Grades");
                        define('submit','Request Evaluation');
                        define('pangaran',"<input type='hidden' name='pangaran' value='Evaluation Fee'></input>");
                        define('bayad',"<input type='hidden' name='bayad' value='40.00'></input>");
                        define('assess_id',"<input type='hidden' name='assess_id' value='3'></input>");
                    }elseif($request=="general"){
                         define('titulo',"General Collection Report");                       
                        define('objective',"<input type='hidden' name='objective' value='general'></input>");

                    }
               }
             
                function pagination_by_20(){
                    $sql_qry=sql_functions::select_query("count(*) as bilang from reg_profile");
                    $row = sql_functions::fetch_to_array($sql_qry);
                    $counting = ceil($row[0]['bilang']/1000);                        
                        ?><center><table cellspacing="0" cellpadding="0"><?php 
                       
                 ?><tr><td colspan = "4" align="center" style="font-size:10pt;">
                      <?php for ($x=1; $x < $counting;$x++){?> 
                                                           <b><a href="main.php?page=registrar&spec=viewall&action=ask&start=<?php echo (($x*1000)-1);?>">
                                                                <?php echo $x ." | ";?>
                                                              </a> 
                                                           </b>
                                                           <?php }?>
                 </td></tr>
                    </table></center><?php        
    }
              
              
              function get_all_names_registrar($start=NULL){
                  if($start!=NULL || $start!=""){
                      $query = sql_functions::select_query("* from reg_profile order by stud_id asc limit ".$start.","."1000");
                  }else{
                      $query = sql_functions::select_query("* from reg_profile order by stud_id asc limit 0,1000"); 
                  }                 
                  $row = sql_functions::fetch_to_array($query);
                  return $row;
              }
              
              function get_all_collection_summary($adlaw){
                  $query = sql_functions::select_query("distinct(una.assess_no),sum(duwa.assess_amount) as amount,duwa.assess_name FROM ssc_cash_sis.collection_".$_SESSION['syear']." as una left join ssc_cash_sis.collectibles_".$_SESSION['syear']." as duwa on una.assess_no = duwa.assess_no where una.or_date ='".$adlaw."' group by assess_name");                  
                  
                #check if there is a record
                   if(sql_functions::count_rows($query)==0){
                       die("YOu have no current collection details");
                   }else{
                         $row = sql_functions::fetch_to_array($query);
                         return $row;
                   }      
              
              }
              
              function insert_summary_collection($data,$adlaw){
                  #insert the summary to excel
                  $assess = array();
                  #prepare first to insert to array
                  foreach($data as $summary){
                      array_push($assess,array('assess_name'=>$summary['assess_name'],'amount'=>$summary['amount']));
                  }
                  #start declaration of excel
                  $excel = new download_excel("reports/collection_summary.xls");
                  $excel->set_cell_value("A2",$adlaw);
                  $excel->set_cell_value("G46",$adlaw);
                  $count=0;
                  $row=7;
                  $total=0;
                  foreach($assess as $ibubutang){
                      $count++;
                      $excel->set_cell_value("A".$row,$count);
                      $excel->set_cell_value("B".$row,$ibubutang['assess_name']);
                      $excel->set_cell_value("G".$row,sprintf("%.2f",$ibubutang['amount']));
                      $total=$total + $ibubutang['amount'];
                      $row++;
                  }
                  $excel->force_download("summary_".$adlaw);
                  $excel->unload_excel();
                  unset($excel);
              }
              
       function enter_dean_grades($data){
                $student = new general_student($data['student_id']);
                $profile = $student->get_profile();
         #1. Check the records and create a table       
              #checks whether a student table exists 
              $table_exists = sql_functions::select_query("count(table_name) as bilang from tables where table_schema='ssc_reg_sis' and table_name='stud_".$data['student_id']."'","information_schema");                 
              $table_count = sql_functions::fetch_to_array($table_exists);
             
          #get the name of the table course
              $course_query =sql_functions::select_query("code from codes where category='prospectus' and description='".$profile[0]['course']."-".$profile[0]['major']."'");
              $his_course =sql_functions::fetch_to_array($course_query);
          #...and create if not existing based from course
              if($table_count[0]['bilang']==0){
                sql_functions::apply_query("create table if not exists stud_".$data['student_id']." as (select * from ".$his_course[0]['code'].")");             
              } 
          #2. Get the rows to update or enter grade 
           
           $query = sql_functions::select_query("* from stud_".$data['student_id']." where yr_lvl=".$data['yr_lvl']." and semester=".$data['semester']." group by subj_code order by yr_lvl,semester,subj_code");
           $row = sql_functions::fetch_to_array($query);
           return $row;
       }
       
       function update_student_grade($grade){
           #this will update the grades of student
           foreach($grade['subj_code'] as $iupdate){
               sql_functions::apply_query("update stud_{$grade['student_id']} set grade_1='{$grade['grade_'.$iupdate]}' ,status='{$grade['stat_'.$iupdate]}' where yr_lvl='{$grade['yr_lvl']}' and semester='{$grade['semester']}' and subj_code='{$iupdate}'");
           }
           echo "You have successfully updated your grade records.";
       }
       
       function get_section(){
           $query = sql_functions::select_query("* from codes where category='section' order by code asc");
           $row = sql_functions::fetch_to_array($query);
           return $row;
       }
       
       function section_him_her($data,$action=NULL){

        if($action=='edit'){
            
        }elseif($action=='delete'){
            
        }else{
            #this function will update the enroll year table for section and also the student ledger table
           $query = sql_functions::select_query("subject from enroll_".$_SESSION['syear']." where student_id='".$data['student_id']."' and yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."' and (subject is not null or subject <>'')");
           if(sql_functions::count_rows($query)==0){
               die("<p class='warnings'>The student bearing id no: <u>".$data['student_id']."</u> has not been assessed.</p>");
           }else{
                $enrolled_subj = sql_functions::fetch_to_array($query);        
             #1. Enroll year table  
               sql_functions::apply_query("update enroll_".$_SESSION['syear']." set section='".$data['section']."' where yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."' and date_registration='".$data['year']."-".$data['month']."-".$data['day']."' and student_id='".$data['student_id']."'");
             #2. Student prospectus table   
             #2.1this is the callback fuction used for updating the student prospectus table          
            function add_quote($element){
                $ikaw = "'".$element."'";
                return $ikaw;
            }   
             #2.2 algorithm start                   
           $ako = explode("|",$enrolled_subj[0]['subject']);
            $ikaw = array_map("add_quote",$ako);
            $ikaw = implode(",",$ikaw);
               sql_functions::apply_query("update stud_".$data['student_id']." set taken_1=concat(taken_1,'".$data['section']."|') where subj_code in (".$ikaw.")");
           }
        }  
           
           
            echo "You have succesfully updated the student's section assignment.";         
                       
       }
       
       function add_student($data){
           #this function will add a student to the registrar profile
           #1. Check first if an id exists
           $id_query = sql_functions::select_query("* from reg_profile where stud_id='".$data['student_id']."'");
               if(sql_functions::count_rows($id_query)==0){
               #2. get first the degreename, major and course
               $query = sql_functions::select_query("* from courses where degreeid='".$data['course']."'");
               $row = sql_functions::fetch_to_array($query);
               #3. start the insertion
               sql_functions::apply_query("insert into reg_profile(stud_id,lname,fname,mname,name,degree_name,course,major,address,gender,civil_status)values('".$data['student_id']."','".strtoupper($data['lname'])."','".strtoupper($data['fname'])."','".strtoupper($data['mname'])."','".strtoupper($data['lname'].", ".$data['fname']." ".substr($data['mname'],0,1))."','".$row[0]['degreename']."','".$row[0]['degreecode']."','".$row[0]['major']."','".$data['address']."','".$data['gender']."','".$data['civil_status']."')");
               echo "You have successfully added a student.";
           }else{
               die("<p class='warnings'>Id no: ".$data['student_id']." already exists from the database</p>");
           }
       }
       
       function show_what_to_add($data){
           $query = sql_functions::select_query("* from stud_".$data['student_id']." where yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."' order by yr_lvl,semester, subj_code");
           $row = sql_functions::fetch_to_array($query);
           return $row;
           
       }
       
       function add_teacher($data){
           $teacher = new teacher_actions($data['teacher_id']);
           if($teacher->check_table_exists($data)){
               $teacher->create_ledger();//create ledger
               $teacher->insert_to_ledger($data);
           }else{
                die("<p class='warnings'>There is already an id related to id no: ".$data['teacher_id']."</p>");
           }
           unset($teacher)
;       }

        function change_to_words($number){
            $number = explode(".",$number);
            $peso = miscellaneous::convert_number_to_words($number[0])." pesos ";
            if(isset($number[1]) && $number[1]!="00"){
                $peso.=" and ".miscellaneous::convert_number_to_words($number[1])." cents only";
            }else{
                $peso.="only";
            }
            return $peso;
        }
        
        function get_religion(){
            $query = sql_functions::select_query("* from codes where category ='religion' order by description asc");
            return sql_functions::fetch_to_array($query);
        }
        
        function get_status_admission(){
             $query = sql_functions::select_query("* from codes where category ='status_admin' order by description asc");
            return sql_functions::fetch_to_array($query);
        }
        
        function replace_code_to_string($code){
            #this function will replace the code of courses to its description
            $query = sql_functions::select_query("* from courses where degreeid='".$code."'");
            $row = sql_functions::fetch_to_array($query);
            return $row[0]['degreecode']."-".$row[0]['major']; 
        }
        
         function replace_code_to_string_status($code){
            #this function will replace the code of courses to its description
            $query = sql_functions::select_query("* from codes where category='status_admin' and code='".$code."'");
            $row = sql_functions::fetch_to_array($query);
            return $row[0]['description'];
        }
        
        function drop_subject($data){
            #this function will drop the subjects in enroll table and student table
            $idadrop = array();
            foreach($data['subjects'] as $subject){
                if($data['action_'.$subject]=="drop"){
                    sql_functions::apply_query("update stud_".$data['student_id']." set status=concat(status,'|','dropped') where subj_code='".$subject."'");
                    array_push($idadrop,$subject);
                }
            }
            $query = sql_functions::select_query("* from enroll_".$_SESSION['syear']." where student_id='".$data['student_id']."' and yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."'");
            $row = sql_functions::fetch_to_array($query);
            #get thhe subjects
            $subjects = $row[0]['subject'];
            $subjects = explode("|",$subjects);
            $i=0;
            foreach($subjects as $subject){
                if($subject=="" || $subject==NULL){
                    unset($subjects[$i]);
                }
                $i++;
            }
            $subjects_polished = array_diff($subjects,$idadrop);
            $subjects_polished = implode("|",$subjects_polished);
            #update the enroll table
            sql_functions::apply_query("update enroll_".$_SESSION['syear']." set subject='".$subjects_polished."' where yr_lvl='".$data['yr_lvl']."' and semester='".$data['semester']."' and student_id='".$data['student_id']."'");
            
            echo "YOu have successfully dropped the subjects";
        }
        
        function get_department(){
            $query = sql_functions::select_query("distinct(department) as department from courses");
            $row = sql_functions::fetch_to_array($query);
            $department = array();
            foreach($row as $departments){
                array_push($department,strtoupper($departments['department']));
            }
           #clean null values
            $i=0;
            foreach($department as $deparments){
              if($departments=="" || $deparments==NULL){
                  unset($department[$i]);
              }  
              $i++;
            }
            return $department;
        }
        
        function get_ar_report($adlaw){
                $course = explode("-",$adlaw['degreename']); 
                $adlaw = $adlaw['year']."-".$adlaw['month']."-".$adlaw['day'];      
                $query = sql_functions::select_query("collectibles.semester,collectibles.id,profile.name,collectibles.amount,datediff('".$adlaw."','2012-12-01') as deperencia FROM collectibles_".$_SESSION['syear']." as collectibles inner join cash_profile as profile on collectibles.student_id=profile.student_id where course='".$course[0]."' and major='".$course[1]."' and assess_id='73' and assess_name='tuition fee balance' and remarks<>'paid' having deperencia>90 order by collectibles.assess_date ","cashier");
                $row = sql_functions::fetch_to_array($query);
                return $row;
        }
        
        function ar_to_excel($rows){
                $excel = new download_excel("reports/accounts_receivable.xls");
                $excel->set_cell_value("A3","AS OF ".strtoupper(date("F")).date("d").", ".date("Y"));
                //initialize variables
                $count = 1;
                $row=7;
                foreach($rows as $value){
                    $excel->set_cell_value("A".$row,$count); //count
                    $excel->set_cell_value("B".$row,$value['name']);
                    $excel->set_cell_value("C".$row,$value['amount']); //amount
                    
                     #conditional statement for the three columns
                    if($value['deperencia']<=90){
                        $excel->set_cell_value("D".$row,$value['amount']);
                    }elseif($value['deperencia']>90 && $value['deperencia']<=365){
                        $excel->set_cell_value("E".$row,$value['amount']); 
                    }elseif($value['deperencia']>365 && $value['deperencia']<=730){
                         $excel->set_cell_value("F".$row,$value['amount']); 
                    }else{
                         $excel->set_cell_value("G".$row,$value['amount']);
                    }
                    
                    $semester =  $name['semester']==1?"1st":"2nd";
                    $semester = $semester." Sem ".$_SESSION['syear']."-".($_SESSION['syear'] + 1);
                    
                    $excel->set_cell_value("H7",$semester);
                   $row++;
                   $count++; 
                }
                $excel->force_download("accounts_receivable_".date('y-m-d')."xls");
                $excel->unload_excel();
                
        }
        
        function download_receipt($data){
            
            $excel = new download_excel("reports/admission_receipt.xls");
            $excel->set_cell_value("c7",strtoupper($data['name']));
            $excel->set_cell_value("c8",$data['gender']);
            $excel->set_cell_value("c9",strtoupper($data['address']));
            $excel->set_cell_value("c10",$data['registration_no']);
            $excel->set_cell_value("c11",$data['date_of_registration']);      
            //*******
            $excel->set_cell_value("c14",$data['first_choice']); 
            $excel->set_cell_value("c15",$data['second_choice']);
            $excel->set_cell_value("c16",$data['third_choice']);    
            $excel->force_download("admission_no_".$data['registration_no']);     
        }
        
            
}   
?>